A database transaction is a unit of interaction with a database management system that is serviced by the management system independent of other transactions. Ideally, a database management system guarantees the properties of atomicity, consistency, isolation and durability for each transaction. A distributed transaction performs the work for the transaction at two or more network nodes. Each network node may have a resource manager, such as a database server, that resides on the network node to service client requests for a distributed transaction. A transaction manager, such as a component of an application server, may coordinate the completion or recovery of a distributed transaction on all resource managers involved in the transaction.
Resource managers running on network nodes may operate in a shared mode such that multiple instances of the resource managers can concurrently read and write to the same resource. For example, a database management system can have database server instances on multiple nodes that are connected to each other with a high-speed network in a clustered environment and share the same data files on disk. Such clustered disk-sharing database systems are commercially available from Oracle Corporation (e.g., Oracle 10g or later versions), known as Oracle Real Application Clusters (RAC). A load balancer of a clustered database system may then route the requests made by the clients to any database server in the system and it is beneficial for each database server in the clustered environment to be able to service the requests made by external clients as a single database management system.
Application Programming Interfaces (API) for distributed transactions, such as implementations of the Distributed Transaction Processing: The XA Specification standard, are used by transaction managers to communicate with the resource managers. Standards for APIs provide an agreed-upon set of guidelines for interoperability. The XA standard interface includes commands (e.g. xa_end( )) for the transaction manager to disassociate an active transaction from any sessions between the transaction manager and the resource managers. For example, a transaction manager can request to disassociate with the resource manager session and later request to prepare to commit the transaction. To prepare to commit the transaction, the resource manager will need to respond with the preparation status for all resource managers involved in committing the transaction. In a clustered database system that uses load balancing, a preparation to commit request may not be routed to the same resource manager for which the prior work for the transaction and corresponding transaction information is stored. If a resource manager that receives a preparation to commit client request for a transaction does not have the transaction information, then the resource manager cannot service the preparation to commit request. Thus, it would be beneficial for the resource manager to allow access to the transaction information for subsequent transaction requests after the session has been disassociated from the transaction.
Approaches for implementing the interface standards for distributed transactions restrict the transaction manager to accessing the same resource manager instance that was used to perform work for the transaction. This is because the transaction manager is restricted to using only one resource manager for the duration of the transaction (e.g. implementing a Singleton design pattern). For example, as shown in FIG. 1, the Client Application 100 requests that the Transaction Manager 102 perform work for a transaction. The Transaction Manager 102 uses Interface 104 methods to communicate with the Database Cluster 106 that has Database Storage 116 for the data of the Database Cluster 106. Initially, the Database Cluster 106 routes the requests for transaction “trx1” to Resource Manager1 108 instead of Resource Manager2 110 and the transaction information for transaction “trx1”, trx1 Information 112, is stored in the Mernory1 114. All subsequent Interface 104 requests to the Database Cluster 106 will be restricted to the database server instance Resource Manager1 108. In order to provide access to the instance of the database server with the transaction information, Resource Manager1 108 may reserve the session for the Transaction Manager 102 for the duration of the transaction. The consequences for reserving the session for a transaction manager for the duration of a transaction results in a significant strain on the resources of a database management system. Thus, there is a need for a solution to implement the interface without straining resources of the database management system.